11 - Spatial & Temporal Data

What is the area of Northern Ireland?

SELECT ST_AREA(boundpath)*1406 
    as "Area (km^2)" from political_unit JOIN boundary 
    ON unitname = 'Northern Ireland'
    WHERE political_unit.unitcode = boundary.unitcode;

How close is the computed value to that reported in Wikipedia?

Wikipedia reports 13,843 km². A finer grid would increase accuracy.

What is the eastern most city in Northern Ireland?

SELECT east.cityname FROM city east JOIN political_unit
    ON east.unitcode = political_unit.unitcode 
    WHERE unitname = 'Northern Ireland' 
    AND NOT EXISTS    
    (SELECT * FROM city other WHERE ST_X(other.cityloc) > ST_X(east.cityloc));

Modify the example geometry database design to include

Historic buildings in a city
Walking paths in a city
Use of the MULTIPOLYGON data type to indicate a political region’s boundary

CREATE TABLE political_unit (
  unitname          VARCHAR(30),
  unitcode          CHAR(2),
  unitpop           DECIMAL(6,2),
  boundaries        MULTIPOLYGON NOT NULL,
      PRIMARY KEY (unitcode));

CREATE TABLE city (
  cityname          VARCHAR(30),
  cityloc           POINT NOT NULL,
  unitcode          CHAR(2),
  historicbuildings MULTIPOINT NULL,
  walkingpaths      MULTILINESTRING NULL,
  PRIMARY KEY (cityname, unitcode),
    CONSTRAINT fk_city_polunit FOREIGN KEY(unitcode) 
       REFERENCES political_unit(unitcode));